﻿using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;
using System.Data.Common;
using BOHVote.Core;

namespace BOHVote.DAL
{
    public class ApplyTest
    {
        Database db;
        public ApplyTest()
        {
            db = DatabaseFactory.CreateDatabase();
        }

        public DataSet getAllTestResult(string ApplyPeriod,string IDCard,string ApplyName,string GovernID,string UnitID)
        {
            DataSet dst = new DataSet();
            
            string strSP = "TestResult_SP";
            DbCommand dbcommand = db.GetStoredProcCommand(strSP);
            db.AddInParameter(dbcommand, "@ApplyPeriodID", DbType.String, ApplyPeriod);
            db.AddInParameter(dbcommand, "@UnitID", DbType.String, UnitID);
            db.AddInParameter(dbcommand, "@IDCard", DbType.String, IDCard);
            db.AddInParameter(dbcommand, "@ApplyName", DbType.String, ApplyName);
            db.AddInParameter(dbcommand, "@GovernID", DbType.String, GovernID);
            
            dst = db.ExecuteDataSet(dbcommand);

            return dst;
        }


        public DataSet getAllTestType()
        {
            string strSQL = "SELECT * FROM TestType WHERE 1 =1 ";

            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteDataSet(db, dbcommand);
           
        }

        public DataSet getActiveTestType()
        {
            string strSQL = "SELECT * FROM TestType WHERE 1 =1 AND Status='A' ";

            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteDataSet(db, dbcommand);
        }

        public DataSet getUnActiveTestType()
        {
            string strSQL = "SELECT * FROM TestType WHERE 1 =1 AND Status<>'A' ";

            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteDataSet(db, dbcommand);
        }

        public DataSet getALLApplyIDInResult(string ApplyPeriod)
        {
            string strSQL = "SELECT distinct atr.ApplyID FROM ApplyBase ab INNER JOIN ApplyTestResult atr ON ab.ApplyID=atr.ApplyID WHERE ab.ApplyPeriodID='" + ApplyPeriod + "' ";

            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteDataSet(db, dbcommand);
        }

        public int TestResultCount(string ApplyID, string ApplyPeriod,string TestType)
        {
            string strSQL = "SELECT Count(0) FROM ApplyTestResult WHERE ApplyID = " + ApplyID + " AND ApplyPeriodID='" + ApplyPeriod + "' AND TestType = " + TestType + " ";

            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Convert.ToInt32(Utility.ExecuteScalar(db, dbcommand));
        }

        #region <<  Modify  >>

        public int InsertNewTestResule(string ApplyID, string TestType, string testResule,string ApplyPeriodID)
        {
            int returnValue = -1;

            string strSQL = "INSERT INTO ApplyTestResult (ApplyID,TestType,TestResult,ApplyPeriodID)VALUES(" + ApplyID + "," + TestType + "," + testResule + ",'"+ApplyPeriodID+"' )";

            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            returnValue = (int)Utility.ExecuteNonQuery(db, dbcommand);

            return returnValue;
        }

        public int UpdateTestResult(string ApplyID, string TestType, string testResult, string ApplyPeriodID)
        {
            int returnValue = -1;

            string strSQL = "UPDATE ApplyTestResult SET TestResult=" + testResult + " WHERE ApplyID= " + ApplyID + " AND TestType=" + TestType + " AND ApplyPeriodID='" + ApplyPeriodID + "' ";

            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            returnValue = (int)Utility.ExecuteNonQuery(db, dbcommand);

            return returnValue;
        }

        public int InsertNewTestType(string TestTypeName, string status)
        {
            int returnValue;

            string strSQL = "INSERT INTO TestType(TestTypeName,status)VALUES('" + TestTypeName + "','" + status + "');SELECT @@IDENTITY AS SEQUENCE";

            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            returnValue  = Convert.ToInt32(Utility.ExecuteScalar(db, dbcommand));

            return returnValue;
        }

        public int UpdateNewTestType(string TestTypeName, string status, string TestTypeID)
        {
            string strSQL = "UPDATE TestType SET TestTypeName='" + TestTypeName + "',status='" + status + "'  WHERE TestTypeID = " + TestTypeID + "";

            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteNonQuery(db, dbcommand);

        }

        public int DeleteTestType(string TestTypeID)
        {
            string strSQL = "DELETE FROM TestType WHERE TestTypeID = " + TestTypeID + " ";

            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteNonQuery(db, dbcommand);
        }
        #endregion
    }
}
